package com.hung.pro.iFlashCards.dao;

import com.hung.pro.iFlashCards.bean.LexiconBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: hung
 * Date: 2008/05/01
 * Time: 22:21:19
 * To change this template use File | Settings | File Templates.
 */
public class LexiconBeanDao extends DBDao {
    public LexiconBeanDao(String dbName) {

        super(dbName);
    }

    public LexiconBeanDao() {
        super();
    }

    public List<LexiconBean> getLexicon() throws Exception {
        List<LexiconBean> lexiconList = new ArrayList<LexiconBean>();
        try {
            Statement stmSelect = this.getConnection().createStatement();
            ResultSet result = stmSelect.executeQuery(SELECT_LEXICON);
            while (result.next()) {
                int id = result.getInt(LexiconBean.COLUMN_ID);
                String name = result.getString(LexiconBean.COLUMN_NAME);
                LexiconBean lexicon = new LexiconBean(id, name);
                lexiconList.add(lexicon);
            }
        } catch (SQLException e) {
            throw e;
        }

        return lexiconList;
    }

    public int insertLexicon(LexiconBean lexicon) throws Exception {
        int id = -1;
        try {
            PreparedStatement stmInsert = this.getConnection().prepareStatement(INSERT_LEXICON, Statement.RETURN_GENERATED_KEYS);
            stmInsert.setString(1, lexicon.getName());
            stmInsert.executeUpdate();
            //get last updated key
            ResultSet rs = stmInsert.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
            return id;
        } catch (SQLException e) {
            throw e;
        }

    }

    public void deletetLexiconByName(String name) throws Exception {
        try {
            PreparedStatement stmInsert = this.getConnection().prepareStatement(DELETE_LEXICON_BY_NAME);
            stmInsert.setString(1, name);
            stmInsert.executeUpdate();
        } catch (SQLException e) {
            throw e;
        }

    }

    public void deleteMultiLexiconItems(List<LexiconBean> lexiconList) throws Exception {
        String lexiconIds = "";
        for (LexiconBean lexicon : lexiconList) {
            lexiconIds = lexiconIds + lexicon.getId() + ",";
        }
        //remove last comma
        if (!"".equals(lexiconIds.toString())) {
            lexiconIds = lexiconIds.substring(0, lexiconIds.length() - 1);
        }
        String deleteSQL = String.format(DELETE_MULTI_LEXICON, lexiconIds);
        //delete from db
        try {
            Statement stmDelete = this.getConnection().createStatement();
            stmDelete.executeUpdate(deleteSQL);
        } catch (SQLException e) {
            throw e;
        }

    }

    public void deleteMultiLexiconItems(LexiconBean[] lexicons) throws Exception {
        List<LexiconBean> lexiconList = Arrays.asList();
        deleteMultiLexiconItems(lexiconList);
    }

    public void deletetLexiconById(int id) throws Exception {
        try {
            PreparedStatement stmInsert = this.getConnection().prepareStatement(DELETE_LEXICON_BY_ID);
            stmInsert.setInt(1, id);
            stmInsert.executeUpdate();
        } catch (SQLException e) {
            throw e;
        }

    }

    public void updatetLexicon(LexiconBean lexicon) throws Exception {
        try {
            PreparedStatement stmUpdate = this.getConnection().prepareStatement(UPDATE_LEXICON);
            stmUpdate.setString(1, lexicon.getName());
            stmUpdate.setInt(2, lexicon.getId());
            stmUpdate.executeUpdate();

        } catch (SQLException e) {
            throw e;
        }

    }
    /************SQL**************/
    /**
     * Insert lexicon
     */
    private static final String INSERT_LEXICON = "" +
            "INSERT INTO LEXICON (NAME) VALUES(?)";

    /**
     * select lexicon
     */
    private static final String SELECT_LEXICON = "SELECT * FROM LEXICON";

    /**
     * update lexicon
     */
    private static final String DELETE_LEXICON_BY_NAME = "DELETE FROM LEXICON WHERE NAME = ?";

    /**
     * delete lexicon by id
     */
    private static final String DELETE_LEXICON_BY_ID = "DELETE FROM LEXICON WHERE ID = ?";

    /**
     * Delete multi lexicon by id
     */
    private static final String DELETE_MULTI_LEXICON = "DELETE FROM LEXICON WHERE ID IN (%s)";

    /**
     * update lexicon
     */
    private static final String UPDATE_LEXICON = "UPDATE LEXICON SET NAME = ? WHERE ID = ?";

}
